package DAO;

// <editor-fold defaultstate="collapsed" desc="HeaderFiles">
import Exception.KwickKioskDBException;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.sql.*;
import java.util.*;
import DTO.Store;
import DTO.Address;
// </editor-fold>

/**
 * This class represents abstraction for data access from store table in database.
 * This class is called from DAO as a proxy.
 * @see DAO
 * @Date Mar 30, 2011
 * @author Tony Lei
 */
public class StoreDAO {

    private Connection conn;
    private AddressDAO addressDAO;
    private static final Logger logger = Logger.getLogger(StoreDAO.class.getName());

    /**
     * constructor for this class.
     * @param conn connection object
     */
    public StoreDAO(Connection conn) {
        this.conn = conn;
        addressDAO = new AddressDAO(conn);
    }

    /**
     * This method adds a new store in database.
     * @param storeName string for name of store
     * @param phone string for phone number
     * @param taxCode string for tax code for the store
     * @param aptNo string for apartment number
     * @param stNo integer for street number
     * @param streetName string for name of street
     * @param postCode  string for postal code associated with location of store
     * @param city string for city where the store is located.
     * @param province a string for province
     * @return a boolean flag
     * @throws {@link KwickKioskDBException}
     */
    public boolean addNewStore(String storeName, String phone, String taxCode,
            String aptNo, int stNo, String streetName,
            String postCode, String city, String province)
            throws KwickKioskDBException {

        int aid =
                addressDAO.addNewAddress(aptNo, stNo, streetName,
                postCode, city, province);
        if (aid == -1) {
            return false;
        }

        String sqlText = "INSERT INTO store "
                + "(storeName, phone, taxCode, aid) VALUES ("
                + "'" + storeName + "', "
                + "'" + phone + "', "
                + "'" + taxCode + "', "
                + aid + ")";
        boolean result = updateDB(sqlText);
        if (!result) {
            addressDAO.removeAddress(aid);
        }
        return result;
    }

     /**
     * This method updates the store information in database.
     * @param originalStoreName string for earlier  name of store
     * @param store a store data transfer object
     * @return a boolean flag
     * @throws {@link KwickKioskDBException}
     */
    public boolean updateStore(String originalStoreName, Store store)
            throws KwickKioskDBException {
        String sqlText = "update store set "
                + "storeName='" + store.getStoreName() + "', "
                + "phone='" + store.getPhone() + "', "
                + "taxCode='" + store.getTaxCode() + "' "
                + "where storeName='" + originalStoreName + "'";

        if (!addressDAO.updateAddress(store.getAddress())) {
            return false; // problem with updateDB is not work, updateAddress work
        }

        return updateDB(sqlText);
    }

    /**
     * This method removes store information from database.
     * @param storeName a string for name of store.
     * @return a boolean flag
     * @throws {@link KwickKioskDBException}
     */
    public boolean removeStore(String storeName)
            throws KwickKioskDBException {

        Statement sqlStatement;
        boolean result = false;
        try {
            sqlStatement = conn.createStatement();
            String sqlText = "select aid from store where storeName='"
                    + storeName + "'";
            ResultSet rs = sqlStatement.executeQuery(sqlText);

            sqlText = "DELETE FROM store WHERE storeName='"
                + storeName + "'";
            result = updateDB(sqlText);

            if (result) {
                if (rs.next()) {
                    result = addressDAO.removeAddress(rs.getInt(1));
                }
            }
        } catch(SQLException ex) {
            logger.log(Level.SEVERE, null, "StoreDAO : removeAddress" + ex);
            throw new KwickKioskDBException("StoreDAO : removeAddress",
                    ex.getMessage());
        }
        return result;
    }

   /**
     * This method retrieves all stores in a province.
     * @param province a string for province
     * @return list of stores
     * @throws {@link KwickKioskDBException}
     */
    public ArrayList<Store> searchByProvince(String province)
            throws KwickKioskDBException {

        Statement sqlStatement;
        ArrayList<Store> list = new ArrayList<Store>();
        try {
            sqlStatement = conn.createStatement();
            String sqlText = "select * "
                    + "from store s, address a "
                    + "where s.aid=a.aid and a.province like '" + province + "'";
            ResultSet rs = sqlStatement.executeQuery(sqlText);
            Address address = null;
            while (rs.next()) {
                address = new Address(rs.getInt("aid"), null, rs.getInt("stNo"),
                        rs.getString("street"),
                        rs.getString("postCode"),
                        rs.getString("city"),
                        rs.getString("province"));

                list.add(new Store(rs.getString("storeName"), rs.getString("phone"),
                        rs.getString("taxCode"), address));
            }
            rs.close();
            sqlStatement.close();
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, null, "StoreDAO : searchByProvince" + ex);
            throw new KwickKioskDBException("StoreDAO : searchByProvince",
                    ex.getMessage());
        }
        return list;
    }

    /**
     * This method searches stores based on city as search criteria.
     * @param city  a string for city
     * @return list of store objects
     * @throws {@link KwickKioskDBException}
     */
    public ArrayList<Store> searchByCity(String city)
            throws KwickKioskDBException {

        Statement sqlStatement;
        ArrayList<Store> list = new ArrayList<Store>();
        try {
            sqlStatement = conn.createStatement();
            String sqlText = "select * "
                    + "from store s, address a "
                    + "where s.aid=a.aid and a.city like '" + city + "'";
            ResultSet rs = sqlStatement.executeQuery(sqlText);
            Address address = null;
            while (rs.next()) {
                address = new Address(rs.getInt("aid"), null, rs.getInt("stNo"),
                        rs.getString("street"),
                        rs.getString("postCode"),
                        rs.getString("city"),
                        rs.getString("province"));

                list.add(new Store(rs.getString("storeName"), rs.getString("phone"),
                        rs.getString("taxCode"), address));
            }
            rs.close();
            sqlStatement.close();
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, null, "StoreDAO : searchByCity" + ex);
            throw new KwickKioskDBException("StoreDAO : searchByCity",
                    ex.getMessage());
        }
        return list;
    }

    /**
     * This method searches stores based on post code  as search criteria.
     * @param postCode a string
     * @return list of stores
     * @throws {@link KwickKioskDBException}
     */
    public ArrayList<Store> searchByPostCode(String postCode)
            throws KwickKioskDBException {

        Statement sqlStatement;
        ArrayList<Store> list = new ArrayList<Store>();
        try {
            sqlStatement = conn.createStatement();
            String sqlText = "select * "
                    + "from store s, address a "
                    + "where s.aid=a.aid and a.postCode like '" + postCode + "'";
            ResultSet rs = sqlStatement.executeQuery(sqlText);
            Address address = null;
            while (rs.next()) {
                address = new Address(rs.getInt("aid"), null, rs.getInt("stNo"),
                        rs.getString("street"),
                        rs.getString("postCode"),
                        rs.getString("city"),
                        rs.getString("province"));

                list.add(new Store(rs.getString("storeName"), rs.getString("phone"),
                        rs.getString("taxCode"), address));
            }
            rs.close();
            sqlStatement.close();
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, null, "StoreDAO : searchPostCode" + ex);
            throw new KwickKioskDBException("StoreDAO : searchByPostCode",
                    ex.getMessage());
        }
        return list;
    }

    /**
     * This method searches an item in database by item name as criteria.
     * @param keywords  string representing search criteria
     * @return array list object of item data transfer objects
     * @throws {@link KwickKioskDBException}
     */
    public ArrayList<Store> searchByName(String name)
            throws KwickKioskDBException {

        Statement sqlStatement;
        ArrayList<Store> list = new ArrayList<Store>();
        try {
            sqlStatement = conn.createStatement();
            String sqlText = "select * "
                    + "from store s, address a "
                    + "where s.aid=a.aid and s.storeName like '%" + name + "%'";
            ResultSet rs = sqlStatement.executeQuery(sqlText);
            Address address = null;
            while (rs.next()) {
                address = new Address(rs.getInt("aid"), null, rs.getInt("stNo"),
                        rs.getString("street"),
                        rs.getString("postCode"),
                        rs.getString("city"),
                        rs.getString("province"));

                list.add(new Store(rs.getString("storeName"), rs.getString("phone"),
                        rs.getString("taxCode"), address));
            }
            rs.close();
            sqlStatement.close();
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, null, "StoreDAO : searchByName" + ex);
            throw new KwickKioskDBException("StoreDAO : searchByName",
                    ex.getMessage());
        }
        return list;
    }

    /**
    * This method searches stores based on phone as search criteria.
    * @param phone a string
    * @return list of stores
    * @throws {@link KwickKioskDBException}
    */
    public ArrayList<Store> searchByPhone(String phone) 
            throws KwickKioskDBException {

        Statement sqlStatement;
        ArrayList<Store> list = new ArrayList<Store>();
        try {
            sqlStatement = conn.createStatement();
            String sqlText = "select * "
                    + "from store s, address a "
                    + "where s.aid=a.aid and s.phone='" + phone + "'";
            ResultSet rs = sqlStatement.executeQuery(sqlText);
            Address address = null;
            while (rs.next()) {
                address = new Address(rs.getInt("aid"), null, rs.getInt("stNo"),
                        rs.getString("street"),
                        rs.getString("postCode"),
                        rs.getString("city"),
                        rs.getString("province"));

                list.add(new Store(rs.getString("storeName"), rs.getString("phone"),
                        rs.getString("taxCode"), address));
            }
            rs.close();
            sqlStatement.close();
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, null, "StoreDAO : searchByPhone" + ex);
            throw new KwickKioskDBException("StoreDAO : searchByPhone",
                    ex.getMessage());
        }
        return list;
    }

   /**
     * This method retrieves all stores from database.
     * @return a list of store objects.
     * @throws {@link KwickKioskDBException}
     */
    public ArrayList<Store> getAllStores() throws KwickKioskDBException {

        Statement sqlStatement;
        ArrayList<Store> list = new ArrayList<Store>();
        try {
            sqlStatement = conn.createStatement();
            String sqlText = "select * "
                    + "from store s, address a "
                    + "where s.aid=a.aid";
            ResultSet rs = sqlStatement.executeQuery(sqlText);
            Address address = null;
            while (rs.next()) {
                address = new Address(rs.getInt("aid"), null, rs.getInt("stNo"),
                        rs.getString("street"),
                        rs.getString("postCode"),
                        rs.getString("city"),
                        rs.getString("province"));

                list.add(new Store(rs.getString("storeName"), rs.getString("phone"),
                        rs.getString("taxCode"), address));
            }
            rs.close();
            sqlStatement.close();
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, null, "StoreDAO : getALLStores" + ex);
            throw new KwickKioskDBException("StoreDAO : searchByProvince",
                    ex.getMessage());
        }
        return list;
    }

     /**
     * This method retrieves tax rate associated with province.
     * @param province a string for province
     * @return a double value for tax rate
     * @throws {@link KwickKioskDBException}
     */
    public double getTaxRate(String province) throws KwickKioskDBException {
        double taxRate = 0.0;
        Statement sqlStatement;
        try {
            sqlStatement = conn.createStatement();
            String sqlText = "select t.taxRate "
                    + "from tax t "
                    + "where t.taxRate='" + province + "'";
            ResultSet rs = sqlStatement.executeQuery(sqlText);
            if (rs.next()) {
                taxRate = rs.getInt(1);
            }
            sqlStatement.close();
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, null, "StoreDAO : getTaxRate" + ex);
            throw new KwickKioskDBException("StoreDAO : searchByProvince",
                    ex.getMessage());
        }
        return taxRate;
    }

     /**
     * this method updates the database
     * @param sqlText a string for data manipulation  query
     * @return a boolean flag
     * @throws {@link KwickKioskDBException}
     */
    private boolean updateDB(String sqlText) throws KwickKioskDBException {
        Statement sqlStatement;
        try {
            sqlStatement = conn.createStatement();
            int value = sqlStatement.executeUpdate(sqlText);
            conn.commit();
            sqlStatement.close();
            if (value == 1) {
                return true;
            }
        } catch (SQLException ex) {
            try {
                conn.rollback();
                logger.log(Level.SEVERE, null, "StoreDAO : updateDB" + ex);
                throw new KwickKioskDBException("StoreDAO : updateDB",
                        ex.getMessage());
            } catch (SQLException ex2) {
                logger.log(Level.SEVERE, null, "StoreDAO : updateDB" + ex2);
                throw new KwickKioskDBException("StoreDAO : updateDB",
                        ex2.getMessage());
            }
        }

        return false;
    }
}
